using System;
using System.Collections.Generic;
using System.Data;

namespace ADEVSO.Data
{
    /// <summary>
    /// Abstract Class Connection
    /// </summary>
    public abstract class Connection
    {
        #region Variables

        protected IDbConnection _InnerConnection;

        #endregion

        #region Constructors

        /// <summary>
        /// Constructor
        /// </summary>
        public Connection()
        {
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionString"></param>
        public Connection(String connectionString)
        {
        }

        #endregion

        #region Properties

        /// <summary>
        /// Create Database Connection
        /// </summary>
        public IDbConnection InnerConnection
        {
            get
            {
                return _InnerConnection;
            }
            set
            {
                _InnerConnection = value;
                if (_InnerConnection != null && _InnerConnection.State != System.Data.ConnectionState.Closed)
                    _InnerConnection.Close();
            }
        }

        /// <summary>
        /// Database Connection State
        /// </summary>
        public String State
        {
            get
            {
                return InnerConnection.State.ToString();
            }
        }

        /// <summary>
        /// Last Identity returned after an operation
        /// </summary>
        public Object LastIdentity { get; set; }

        #endregion

        #region Privates

        #endregion

        #region Methods

        /// <summary>
        /// Open Connection
        /// </summary>
        public void Open()
        {
            if (_InnerConnection != null && !InTransaction && _InnerConnection.State == ConnectionState.Closed)
                _InnerConnection.Open();
        }

        /// <summary>
        /// Close Connection
        /// </summary>
        public void Close()
        {
            if (_InnerConnection != null && !InTransaction && _InnerConnection.State != ConnectionState.Closed)
                _InnerConnection.Close();
        }

        #region Abstract

        #endregion

        #region Readers

        /// <summary>
        /// Get a DataSet from a Stored Procedure
        /// </summary>
        public DataSet GetDataSet(String storedProcedure)
        {
            return GetDataSet(storedProcedure, null);
        }

        /// <summary>
        /// Get a DataSet from a Stored Procedure with Parameters
        /// </summary>
        public DataSet GetDataSet(String storedProcedure, params Object[] args)
        {
            DataSet mDataSet = new DataSet();
            if (args == null)
                CreateDataAdapter(storedProcedure).Fill(mDataSet);
            else
                CreateDataAdapter(storedProcedure, args).Fill(mDataSet);
            return mDataSet;
        }

        /// <summary>
        /// Get a DataTable from a Stored Procedure
        /// </summary>
        public System.Data.DataTable GetDataTable(String storedProcedure)
        {
            return GetDataTable(storedProcedure, null);
        }

        /// <summary>
        /// Get a DataTable from a Stored Procedure with Parameters
        /// </summary>
        public System.Data.DataTable GetDataTable(String storedProcedure, params Object[] args)
        {
            DataTableCollection tables = null;
            if (args == null)
                tables = GetDataSet(storedProcedure).Tables;
            else
                tables = GetDataSet(storedProcedure, args).Tables;
            return tables.Count > 0 ? tables[0].Copy() : new System.Data.DataTable();
        }

        /// <summary>
        /// Get a Value from a Stored Procedure
        /// </summary>
        public Object GetValue(String storedProcedure)
        {
            return GetValue(storedProcedure, null);
        }

        /// <summary>
        /// Get a Value from a Stored Procedure with Parameters
        /// </summary>
        public Object GetValue(String storedProcedure, params Object[] args)
        {
            IDbCommand command = Command(storedProcedure);
            if (args != null)
                LoadParameters(command, args);
            command.ExecuteNonQuery();
            Object result = null;
            foreach (IDbDataParameter parameter in command.Parameters)
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Output)
                    result = parameter.Value;
            return result;
        }

        /// <summary>
        /// Dispose Database Connection
        /// </summary>
        public String Dispose()
        {
            try
            {
                InnerConnection.Dispose();
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        #endregion

        #region Actions

        public abstract List<String> GetDatabases();
        public abstract List<String> GetTables();
        public abstract List<Sql.PrimaryKey> GetTablePKs(String table);
        public abstract List<Sql.Column> GetTableColumns(String tableName);
        public abstract List<Sql.ForeignKey> GetTableFKs(String table);
        public abstract List<Sql.Identity> GetTableIdentities(String table);
        protected abstract IDbConnection CreateConnection(String connectionString);
        protected abstract IDbCommand CreateCommand(String storedProcedure);
        protected abstract IDbCommand Command(String storedProcedure);
        protected abstract IDataAdapter CreateDataAdapter(String storedProcedure, params Object[] args);
        protected abstract void LoadParameters(IDbCommand command, Object[] args);
        public abstract int SaveImage(String tableName, String idColumnName, String imageColumnName, long id, byte[] image);

        /// <summary>
        /// Execute Stored Procedure
        /// </summary>
        public int Execute(String storedProcedure)
        {
            return Command(storedProcedure).ExecuteNonQuery();
        }

        /// <summary>
        /// Execute Stored Procedure with Parameters
        /// </summary>
        public int Execute(String storedProcedure, params Object[] args)
        {
            IDbCommand command = Command(storedProcedure);
            LoadParameters(command, args);
            int result = command.ExecuteNonQuery();
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                IDbDataParameter parameter = command.Parameters[i] as IDbDataParameter;
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Output)
                {
                    args.SetValue(parameter.Value, i - 1);
                    LastIdentity = parameter.Value;
                }
            }
            return result;
        }

        /// <summary>
        /// ExecuteScalar Stored Procedure
        /// </summary>
        public byte[] ExecuteScalar(String storedProcedure)
        {
            return Command(storedProcedure).ExecuteScalar() as byte[];
        }

        /// <summary>
        /// ExecuteScalar Stored Procedure with Parameters
        /// </summary>
        public byte[] ExecuteScalar(String storedProcedure, params Object[] args)
        {
            IDbCommand command = Command(storedProcedure);
            LoadParameters(command, args);
            byte[] result = command.ExecuteScalar() as byte[];
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                IDbDataParameter parameter = command.Parameters[i] as IDbDataParameter;
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Output)
                {
                    args.SetValue(parameter.Value, i - 1);
                    LastIdentity = parameter.Value;
                }
            }
            return result;
        }

        #endregion

        #region Transactions

        protected List<IDbTransaction> Transactions = new List<IDbTransaction>();
        protected int TransactionsCount;
        protected bool InTransaction
        {
            get
            {
                bool result = false;
                if (Transactions != null && Transactions.Count > 0)
                    result = true;
                return result;
            }
        }

        /// <summary>
        ///  Return Last Transaction created
        /// </summary>
        protected IDbTransaction Transaction
        {
            get
            {
                IDbTransaction result = null;
                if (Transactions != null && Transactions.Count > 0)
                    result = Transactions[Transactions.Count - 1];
                return result;
            }
            set
            {
                if (value == null)
                {
                    Transactions[Transactions.Count - 1].Dispose();
                    Transactions.Remove(Transaction);
                }
            }
        }

        /// <summary>
        /// Begin New Database Transaction
        /// </summary>
        public void BeginTransaction()
        {
            if (!InTransaction)
            {
                if (InnerConnection.State == ConnectionState.Closed)
                    Open();
                Transactions.Add(InnerConnection.BeginTransaction());
            }
            TransactionsCount++;
        }

        /// <summary>
        /// Commit Current Database Transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (InTransaction && TransactionsCount == 1)
            {
                try
                {
                    Transaction.Commit();
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
                finally
                {
                    Transaction = null;
                    Close();
                }
            }
            TransactionsCount--;
        }

        /// <summary>
        /// Abort Current Database Transaction
        /// </summary>
        public void AbortTransaction()
        {
            if (InTransaction)
            {
                try
                {
                    Transaction.Rollback();
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
                finally
                {
                    Transaction = null;
                    TransactionsCount = 0;
                    Close();
                }
            }
        }

        #endregion

        #endregion
    }
}
